const models = require('../db/mysqldb/index')

class ChatPrivateMessageModel {
  /**
   * @description 获取当前用户所有相关聊天信息列表
   * @param {string} userId
   * @returns
   */
  static async findByUserId (userId) {
    //  todo 修改sql
    let sql = `SELECT
              cur.other_user_id AS user_id,
              cur.other_user_alias AS user_alias,
              su.avatar,
              su.realname,
              su.phone,
              su.STATUS,
              su.sex,
              ( SELECT count( 1 ) FROM chat_private_message ucpm WHERE ucpm.delete_timestamp IS NULL AND ucpm.TO = ${userId} AND ucpm.READ = 0 ) AS unread,
              cm.message,
              cm.chat_type,
              cm.message_time
            FROM
              chat_user_relation cur
              INNER JOIN sys_user AS su ON cur.other_user_id = su.user_id 
              INNER JOIN (	SELECT
                  cpm.content AS message,
                  cpm.chat_type AS chat_type,
                  cpm.create_timestamp AS message_time,cpm.from,cpm.to 
                FROM
                  chat_private_message cpm WHERE cpm.delete_timestamp is null
                ORDER BY
                  cpm.create_timestamp DESC 
                  LIMIT 1 ) as cm ON (cm.from = cur.other_user_id AND cm.to = ${userId}) or (cm.to = cur.other_user_id AND cm.from = ${userId})
            WHERE
              cur.user_id = ${userId}
              AND cur.delete_timestamp IS NULL 
              AND su.delete_timestamp IS NULL`
    const result = await models.sequelize.query(sql, {
      replacements: ['active'],
      type: models.sequelize.QueryTypes.SELECT,
    })
    return result
  }

  /**
   * @description 获取私聊相关内容
   * @param {string} to
   * @param {string} from
   * @param {string} limit
   * @param {string} size
   * @returns
   */
  static async getHistoryMessage (to, from, limit, size) {
    let sql = `SELECT * FROM 
        ( select p.from,p.to, p.content ,p.create_timestamp,p.chat_type,i.avatar , i.realname ,i.status from chat_private_message as p 
          inner join sys_user as i on p.from = i.user_id
           where (p.from = ${from} AND p.to = ${to} ) or (p.from = ${to} AND p.to = ${from} ) and p.delete_timestamp is null 
           order by create_timestamp desc limit ${limit} OFFSET  ${(size - 1) * limit
      }) as n order by n.create_timestamp`

    const result = await models.sequelize.query(sql, {
      replacements: ['active'],
      type: models.sequelize.QueryTypes.SELECT,
    })
    return { result }
  }

  /**
   * @description 新增私聊信息
   * @param {string} to
   * @param {string} from
   * @param {string} content
   * @param {string} msg_type
   * @returns
   */
  static async save (from, to, content, msg_type, create_timestamp) {
    return await models.chat_private_message.create({
      from,
      to,
      content,
      msg_type,
      create_timestamp,
    })
  }

  /**
   * @description 获取私聊未读信息
   * @param {string} to
   * @param {string} from
   * @returns
   */
  static async unreadNum (from, to) {
    let sql = `SELECT count(1) as num FROM chat_private_message where from = ${from} AND to = ${to} and status = 0 and delete_timestamp is null`
    const result = await models.sequelize.query(sql, {
      replacements: ['active'],
      type: models.sequelize.QueryTypes.SELECT,
    })
    return result
  }


  /**
   * @description 清除私聊未读信息
   * @param {string} to
   * @param {string} from
   * @returns
   */
  static async clearUnread (from, to) {
    return await models.chat_private_message.update({
      read: 1,
      update_timestamp: Date.now()
    }, {
      where: {
        from, to
      }
    })
  }
}
module.exports = {
  ChatPrivateMessageModel,
}
